﻿    CREATE OR REPLACE FUNCTION get_student(
        name varchar(128) DEFAULT NULL
    ) 
    RETURNS 
    SETOF student 
    AS 
    $$
        /***************************************************************************************************
    **  Function:	public.get_student
    **  Author:		Zhixian Ong
    **
    **  Description:
    **    
    **    Get student list
    **
    **  Parameters (1):
    **
    **      name    VARCHAR(128)    -
    **
    **  Return:
    **
    **      void
    **
    **  Sample call:
    **
            Get all students:
            
                SELECT * FROM get_student();
                
            Get all students named:
            
                SELECT * FROM get_student('Judy');
    **
    **  Modification History:
    **
    **      20140102  Zhixian Ong     - Initial prototype
    ** 
    **  Status:
    **
    **      Dev
    ** 
    ***************************************************************************************************/
    
    SELECT  * 
    FROM    student 
    WHERE   (get_student.name IS NULL)
            OR (
                (get_student.name IS NOT NULL) 
                AND (student.full_name LIKE '%' || get_student.name || '%')
            );
            
    $$ LANGUAGE SQL;
 